﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using SqlSugar;
using System.Drawing;

namespace SqlSugar操作数据库
{
    class Program
    {

        static SqlSugarClient getDb()
        {
            //String connectionString = ConfigurationManager.ConnectionStrings["connSqlServer"].ConnectionString;
            String connectionString = ConfigurationManager.ConnectionStrings["connSqlServer2"].ConnectionString;
            // Console.WriteLine(connectionString);

            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = connectionString,// 连接符字串
                DbType = DbType.SqlServer,
                IsAutoCloseConnection = true // 不设成true要手动 close
            });

            // 调试SQL事件，可以删掉 (要放在执行方法之前)
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                Console.WriteLine(sql); //输出sql,查看执行sql
                // 5.0.8.2 获取无参数化 SQL 
                // UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)
            };
            return db;
        }

        static void Main(string[] args)
        {

            DALDemo demo = new DALDemo();
            //demo.ShowAllBooks();
            demo.FindBooksByName("C#");

            
            

            //SqlSugarClient db = getDb();
            //db.CodeFirst.InitTables<Foo>(); // 创建表


            // 添加
            //addCourse(db);

            // 修改
            // updateCourse(db);

            // 删除
            // deleteCourse(db);

            //showCourses(db);

            //List<Course> courses = null;
            // db.Queryable<Course>().Where(it => it.CourseID > 1).ToList();

            // courses = db.Queryable<Course>()
            //     .Where(it => it.Title == "语文")
            //     .ToList();

            // courses = db.Queryable<Course>()
            //     .Where(it => it.CourseID > 2 && it.Title == "语文")
            //     .ToList();

            //var exp = Expressionable.Create<Course>();
            // 第一个参数用来控制，是否使用后面的查询条件
            //exp.AndIF(true,it=>it.CourseID>2);
            //exp.AndIF(true,it =>it.Title.Contains("语文"));
            //exp.OrIF(true, it => it.Title.Contains("数学"));
            //courses = db.Queryable<Course>()
            //    .Where(exp.ToExpression())
            //    .ToList();
            //for (var i = 0; i < courses.Count; i++)
            //{
            //    Course course = courses[i];
            //    Console.WriteLine("{0},{1},{2}",
            //        course.CourseID,course.Title,
            //        course.Credits);
            //}

            Console.ReadKey();
        }

        private static void deleteCourse(SqlSugarClient db)
        {
            db.Deleteable<Course>(new Course()
            {
                CourseID = 2
            }).ExecuteCommand();
        }

        private static void updateCourse(SqlSugarClient db)
        {
            db.Updateable<Course>(new Course()
            {
                CourseID = 2, Title = "国语", Credits = 90
            }).ExecuteCommand();
        }

        private static void showCourses(SqlSugarClient db)
        {
            List<Course> lists = db.Queryable<Course>().ToList();
            foreach (Course c in lists)
            {
                Console.WriteLine("{0},{1}", c.Title, c.Credits);
            }
        }

        private static void addCourse(SqlSugarClient db)
        {
            db.Insertable<Course>(new Course[]
            {
                new Course() {Title = "语文", Credits = 60},
                new Course() {Title = "数学", Credits = 70},
                new Course() {Title = "英语", Credits = 80}
            }).ExecuteCommand();
        }
    }

    [SugarTable("Course")]
    class Course
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int CourseID { get; set; }

        [SugarColumn(ColumnName = "Title")]
        public String Title { get; set; }

        [SugarColumn(ColumnName = "Credits")]
        public int Credits { get; set; }
    }


    [SugarTable("Foo")]
    class Foo
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }

        [SugarColumn(ColumnName = "Name")]
        public String Name { get; set; }


        [SugarColumn(ColumnName = "Val")]
        public int? Val { get; set; }
    }
}
